﻿create procedure spMOMPortShipping @OwnerID int as
set nocount on
	declare @message nvarchar(1000)
	set @message = 'MOMPortShipping' + '  ' + convert( varchar, getdate(), 9 )
	RAISERROR (@message, 0, 1) WITH NOWAIT

declare @LocationID int
select top 1 @LocationID = LocationID from tblLocation where LocationTypeCode = 'W';

set context_info 0x999

if object_id('tempdb..##box') is null
begin
	select * into ##box from openquery( MOMPort, 
		'select box_id, ship_date, trackingno, shiplist, order, inpart, Calc_Weigh from box' )
	create index idx_tmpBox on ##box ( [box_id] )
end

if object_id('tempdb..##items') is null
begin
	select * into ##items from openquery( MOMPort, 
		'select order, inpart, item, quanto from items' )
	create index idx_tmpitems on ##items ( [order] )
end

declare cBox cursor for select box_id from ##box
declare @box_id int
open cBox
fetch next from cBox into @box_id
while @@fetch_status = 0
begin
	declare @PackageID int

	insert into tblPackage ( CreateDate, ShippedDate, TrackingNumber, BoxID, ConfirmationDate, CarrierCode, PersonID, AddressID, OwnerID, [Weight] )
	select dbo.SmallDTCast(ship_date), dbo.SmallDTCast(ship_date), trackingno, box_id, dbo.SmallDTCast(ship_date), T2.CarrierCode, T4.ShipTo_PersonID, T4.ShipTo_AddressID, @OwnerID, T1.Calc_Weigh
	from ##box T1 
		left outer join codeCarrier T2 on T1.shiplist = T2.abbrev
		join tblAltOrderID T3 with ( index ( IDX_AltOrderIDByValue ) ) on cast(T1.[order] as varchar(20)) = T3.IDValue
			and T3.IDSourceCode = 'E' and OwnerID = @OwnerID
		join tblOrderHeader T4 on T3.OrderID = T4.OrderID
	where box_id = @box_id

	set @PackageID = SCOPE_IDENTITY()

	insert into tblInventoryTransaction ( 
		InventoryTransactionTypeCode,
		InventoryTransactionStatusCode,
		Qty,
		InventoryID,
		EffectiveDate,
		OrderID,
		PackageID )
	select 'S',
		'C',
		0 - Quanto,
		T4.InventoryID,
		dbo.SmallDTCast(T1.ship_date),
		T5.OrderID,
		@PackageID
	from ##box T1 
		join ##items T2 on T1.[order] = T2.[order]
			and T1.inpart = T2.inpart
		join tblProduct T3 on T2.item = T3.ItemCode
			and T3.OwnerID = @OwnerID
		join tblInventory T4 on T3.ProductID = T4.ProductID
			and T4.LocationID = @LocationID
		join tblAltOrderID T5 on cast(T1.[order] as varchar(20)) = T5.IDValue
			and T5.IDSourceCode = 'E' and T5.OwnerID = @OwnerID
	where T1.box_id = @box_id
	
	fetch next from cBox into @box_id
end
close cBox
deallocate cBox;

set CONTEXT_INFO 0

